STATE OF MAINE
DEPARTMENT OF LABOR
45 Commerce Drive
118 SHS
Augusta, ME 04333-0118

LAURA FORTMAN
COMMISSIONER

JOHN ELIAS BALDACCI
GOVERNOR

Configuring Server Disk for Database Performance

Sheldon Bird
Technical Services Manager,
Maine Department of Labor

Databases, for many reasons, present unique performance issues to the administrator when compared with traditional file-and-print services.  It is unfortunate that often, when an administrator is confronted for the first time with configuring a database application server, he/she must make irrevocable decisions with little or no knowledge of the consequences, which can be dire.

Regardless of manufacturer, database management systems have some general characteristics in common.  There is the database itself, which is a logical collection of tables, indices, procedures and rules, which may be divided into one or more physical files.  Also, database management systems use one or more transaction log, before image, or rollback files to maintain integrity in case of failed partial transactions.  There is also usually workspace required for batch or temporary scratch storage.  Each of these file types is unique in behavior, and performance will suffer if they are combined on the same physical disk(s).  The worst choice an administrator can make is to engage in the false economy of lumping everything together on one filesystem, regardless of its configuration.

•	The database tables and indices are subject to intense random reading and writing.
•	The transaction logs are written sequentially during each create or update and are rarely read.
•	Temporary space is often written heavily while the database is under heavy batch load.

These facts make it obvious that for maximum performance, these three types of files must not share physical disk.

Disk drives continue to get faster and cheaper, meaning that there is more to be lost than gained by conserving disk at the expense of performance.  The productivity lost by taking fractions of seconds longer to do a transaction in an economized disk array will more than offset the savings in disk over time.

It is important to understand the effect of different disk configurations on the performance of databases.  Before RAID arrays, administrators used to have to break up their databases manually into many smaller parts spread across different disks to reduce contention.  It was also common to use “asynchronous I/O,” bypassing the disk operating system and allowing the DBMS direct access to physical disk.  Both these practices are now obsolete due to RAID and low cost.

RAID arrays can accomplish one or both of the following: Increased performance and fault tolerance.  There are four relevant RAID methods that are useful in database servers.  See http://www.raidarray.com/raid.html for a nice explanation and graphics.  To avoid confusion over the meaning of “drive,” I will use the word “spindle” to mean a physical drive in the following discussion.  Also, the term “RAID controller” means a software or hardware program that manages RAID arrays.

Magnetic disks have two physical characteristics related to performance: Latency and transfer rate.  Latency is the time between an instruction to access a particular area on the disk and the time that the head is actually in position to access the area, and is expressed as an average.  Transfer rate is the amount of data per second that the drive can deliver through its connection channel.

•	RAID 0: Data is “striped” across multiple spindles.  Data committed by the OS is physically written by the RAID controller in blocks that span the spindles sequentially, so that sequential blocks of data span the array.  The effect is a statistical increase in the likelihood that a particular block of disk will be available to service the request immediately during contention from multiple processes.  In any striping array, the more spindles the better the performance.
•	RAID 1: Disks are in mirrored pairs.  While there is no improvement in performance, and in fact under software control there is a penalty, RAID 1 offers complete redundancy.  A further advantage is that the mirror can be “broken,” allowing a disk to be offline during a system change: If the change fails, the array can be quickly restored from the mirror.  A mirrored pair is faster than a single disk because the controller will access either drive if it is not busy.
•	RAID 5: Often referred to as “parity,” it is a RAID 0 array with an added disk (N+1) which does not count toward capacity.  One block in each stripe is used to store a calculated number that can be used to recover any data from a failed block elsewhere in the stripe.  RAID 5 has all the advantages of RAID 0 with the addition of fault tolerance.  There is however, a significant penalty over RAID 0 or a simple disk (up to 30%) when writing, because the parity block must be read, calculated and re-written each time any data on the stripe is modified.  RAID 5 represents the lowest cost for fault tolerance.  The rule for databases on RAID 5: DON’T!
•	RAID 10, or 0+1: Data is both striped and mirrored.  This requires two disks for every one that counts toward capacity, and is therefore the highest cost, but this array offers the best overall performance and fault tolerance of all arrays because you get the statistical advantage of striping across spindles PLUS the ability to access a mirrored drive if the primary is busy.

Looking back at the types of database files, it becomes obvious that each is suited to a particular array type from a performance standpoint.
•	Database tables and indices: Since access is random and subject to multiple simultaneous requests, striping is essential.  If there is a high frequency of creates and updates, RAID 5’s write penalty can become significant.  Fault tolerance is important.  Therefore, RAID 10 is the best array for this use.
•	Transaction logs (BI and AI files): Written sequentially, so the best performance is achieved by providing a dedicated spindle in order to keep the head in position for writing the next block, avoiding latency.  Since every create or update equals a log entry, waiting for the log file can have a severe impact.  This type of file must go on its own dedicated spindle set.  Since the log file is an integral part of the database, fault tolerance is equally important here.  Therefore, log files must be alone on dedicated RAID 1 arrays.  While this can seem like a waste of space, the performance gain is well worth the money.
•	Temporary space: This is not so cut and dried.  It depends on the way this space is used and the amount of time available for procedures that use it.  In a heavy batch environment, performance can be an issue.  If the data can be recreated easily in event of failure, then RAID 0 is best.  If it cannot, then RAID 10, or if fault tolerance is important but write performance is not, then RAID 5 is the best compromise of performance, fault tolerance and economy.  If temp space is used little, it can go anywhere but the log disks.

Databases and log files must be physically separated from other functions, such as the root or system drive, swap areas, and program libraries to avoid more contention.

For database tables and indices, the more spindles containing smaller amounts of data, the better.

The more disk channels serving the spindles, the better.

A typical database server, then, should have the following layout:

•	The system or root/swap disk: A RAID 1 pair.  Programs can also be located here.
•	The database filesystem: RAID 10, as many disks and channels as you can afford.  At $350 for a high-speed 9 GB disk, there is little to be gained from skimping here.  Multiple databases can be placed on one RAID 10 array, as long as it has lots of spindles.
•	The transaction log(s): a RAID 1 pair for each.
•	Temp space: Suited to the use, but separate if any significant usage, and fault tolerant if needed.  If possible, consider RAMdisk for frequently accessed temp space.  There should be adequate space here for online backups and dump/loads.

Hardware vs. Software RAID: Hardware RAID is generally preferable to software because it offloads the tasks from the CPU.  You have a choice in hardware RAID: You can get a “dumb” hot-swap chassis and a multi-channel controller, allowing manual layout of your arrays, but many RAID chassis have intelligent controllers built in, which interface to the host via a single or redundant channel.  This limits the channel bandwidth, and creates an argument in favor of creating pairs of RAID 1 arrays in multiple chassis, and combining them under CPU software control into mirrored pairs using multiple channels, creating a virtual RAID 10 (0+1) array.

Another disk attribute is worth mentioning Cache.  Cache has the potential for huge performance gains by reducing the amount of physical disk access replaced by fast RAM.  Care must be taken that disk caching does not interfere with internal DBMS buffering and CPU caching (anti-caching).  Most importantly, most controllers default to write-back caching, and generally databases do not support this because it can change the order in which data is written to disk from what was intended by the DBMS, leading to database corruption in event of hardware or power failure!  Unless using a battery-backed cache approved by the database manufacturer, always use write-through caching.

The administrator should also consider the optimal database block size and the optimal stripe block size.  A close working relationship with the database administrator is essential.

It is evident that disk choices are important and that “cost” is not always what it seems.  It is also obvious that the administrator who configures a database server must have an understanding of how the database management system works and how the application(s) will impact the system.  On-line vs. batch, the complexity of the transactions, the number of transactions per second, all affect how the disk subsystem should be configured.